
-- --------------------------------------------------
-- Entity Designer DDL Script for SQL Server 2005, 2008, and Azure
-- --------------------------------------------------
-- Date Created: 07/25/2012 18:00:38
-- Generated from EDMX file: E:\src\project-phoebus\1\MvcApplication9\MvcApplication9\Models\MainModel.edmx
-- --------------------------------------------------

SET QUOTED_IDENTIFIER OFF;
GO
USE [Bunch];
GO
IF SCHEMA_ID(N'dbo') IS NULL EXECUTE(N'CREATE SCHEMA [dbo]');
GO

-- --------------------------------------------------
-- Dropping existing FOREIGN KEY constraints
-- --------------------------------------------------

IF OBJECT_ID(N'[dbo].[FK_ShipCabin]', 'F') IS NOT NULL
    ALTER TABLE [dbo].[Cabins] DROP CONSTRAINT [FK_ShipCabin];
GO
IF OBJECT_ID(N'[dbo].[FK_ShipCost]', 'F') IS NOT NULL
    ALTER TABLE [dbo].[Ships] DROP CONSTRAINT [FK_ShipCost];
GO
IF OBJECT_ID(N'[dbo].[FK_CheckPointRCoursePointR]', 'F') IS NOT NULL
    ALTER TABLE [dbo].[CoursePoints] DROP CONSTRAINT [FK_CheckPointRCoursePointR];
GO
IF OBJECT_ID(N'[dbo].[FK_CourseRCoursePointR]', 'F') IS NOT NULL
    ALTER TABLE [dbo].[CoursePoints] DROP CONSTRAINT [FK_CourseRCoursePointR];
GO
IF OBJECT_ID(N'[dbo].[FK_ExcursionRCheckPointR]', 'F') IS NOT NULL
    ALTER TABLE [dbo].[Excursions] DROP CONSTRAINT [FK_ExcursionRCheckPointR];
GO
IF OBJECT_ID(N'[dbo].[FK_CourseRShipR]', 'F') IS NOT NULL
    ALTER TABLE [dbo].[Ships] DROP CONSTRAINT [FK_CourseRShipR];
GO

-- --------------------------------------------------
-- Dropping existing tables
-- --------------------------------------------------

IF OBJECT_ID(N'[dbo].[Submits]', 'U') IS NOT NULL
    DROP TABLE [dbo].[Submits];
GO
IF OBJECT_ID(N'[dbo].[Comments]', 'U') IS NOT NULL
    DROP TABLE [dbo].[Comments];
GO
IF OBJECT_ID(N'[dbo].[Clients]', 'U') IS NOT NULL
    DROP TABLE [dbo].[Clients];
GO
IF OBJECT_ID(N'[dbo].[Users]', 'U') IS NOT NULL
    DROP TABLE [dbo].[Users];
GO
IF OBJECT_ID(N'[dbo].[Ships]', 'U') IS NOT NULL
    DROP TABLE [dbo].[Ships];
GO
IF OBJECT_ID(N'[dbo].[CostShips]', 'U') IS NOT NULL
    DROP TABLE [dbo].[CostShips];
GO
IF OBJECT_ID(N'[dbo].[Cabins]', 'U') IS NOT NULL
    DROP TABLE [dbo].[Cabins];
GO
IF OBJECT_ID(N'[dbo].[Excursions]', 'U') IS NOT NULL
    DROP TABLE [dbo].[Excursions];
GO
IF OBJECT_ID(N'[dbo].[CheckPoints]', 'U') IS NOT NULL
    DROP TABLE [dbo].[CheckPoints];
GO
IF OBJECT_ID(N'[dbo].[Courses]', 'U') IS NOT NULL
    DROP TABLE [dbo].[Courses];
GO
IF OBJECT_ID(N'[dbo].[CoursePoints]', 'U') IS NOT NULL
    DROP TABLE [dbo].[CoursePoints];
GO

-- --------------------------------------------------
-- Creating all tables
-- --------------------------------------------------

-- Creating table 'Submits'
CREATE TABLE [dbo].[Submits] (
    [Id] int IDENTITY(1,1) NOT NULL,
    [TravelDate] datetime  NOT NULL,
    [CountOfTourists] int  NOT NULL,
    [Name] nvarchar(max)  NOT NULL,
    [Sity] nvarchar(max)  NOT NULL,
    [Email] nvarchar(max)  NOT NULL,
    [Phone] nvarchar(max)  NOT NULL,
    [OtherInfo] nvarchar(max)  NOT NULL,
    [SubmitDate] nvarchar(max)  NOT NULL,
    [CruiseID] int  NOT NULL
);
GO

-- Creating table 'Comments'
CREATE TABLE [dbo].[Comments] (
    [Id] int IDENTITY(1,1) NOT NULL,
    [Login] nvarchar(max)  NOT NULL,
    [Text] nvarchar(max)  NOT NULL,
    [Date] datetime  NOT NULL
);
GO

-- Creating table 'Clients'
CREATE TABLE [dbo].[Clients] (
    [Id] int IDENTITY(1,1) NOT NULL,
    [FName] nvarchar(max)  NULL,
    [SName] nvarchar(max)  NULL,
    [PassportNumber] nvarchar(max)  NOT NULL,
    [PassportSeries] nvarchar(max)  NOT NULL,
    [CurrentAccount] nvarchar(max)  NOT NULL
);
GO

-- Creating table 'Users'
CREATE TABLE [dbo].[Users] (
    [Id] int IDENTITY(1,1) NOT NULL,
    [Login] nvarchar(max)  NOT NULL,
    [Password] nvarchar(max)  NOT NULL,
    [Salt] nvarchar(max)  NOT NULL,
    [Role] nvarchar(max)  NOT NULL,
    [FName] nvarchar(max)  NOT NULL,
    [SName] nvarchar(max)  NOT NULL,
    [BDate] datetime  NOT NULL,
    [Email] nvarchar(max)  NOT NULL,
    [Phone] nvarchar(max)  NOT NULL
);
GO

-- Creating table 'Ships'
CREATE TABLE [dbo].[Ships] (
    [Id] int IDENTITY(1,1) NOT NULL,
    [Name] nvarchar(max)  NOT NULL,
    [Speed] int  NOT NULL,
    [Survey] datetime  NOT NULL,
    [Launch] datetime  NOT NULL,
    [Model] nvarchar(max)  NOT NULL,
    [Autonomy] float  NOT NULL,
    [Cost_Id] int  NOT NULL,
    [Course_Id] int  NOT NULL
);
GO

-- Creating table 'CostShips'
CREATE TABLE [dbo].[CostShips] (
    [Id] int IDENTITY(1,1) NOT NULL,
    [Charge] float  NOT NULL,
    [Profit] float  NOT NULL,
    [Payment] float  NOT NULL
);
GO

-- Creating table 'Cabins'
CREATE TABLE [dbo].[Cabins] (
    [Id] int IDENTITY(1,1) NOT NULL,
    [Level] nvarchar(max)  NOT NULL,
    [ShipId] int  NOT NULL,
    [Total] smallint  NOT NULL,
    [Margin] float  NOT NULL,
    [Description] nvarchar(max)  NOT NULL
);
GO

-- Creating table 'Excursions'
CREATE TABLE [dbo].[Excursions] (
    [Id] int IDENTITY(1,1) NOT NULL,
    [TStart] datetime  NOT NULL,
    [Subject] nvarchar(max)  NOT NULL,
    [Description] nvarchar(max)  NOT NULL,
    [FreeOfCharge] bit  NOT NULL,
    [Price] smallint  NULL,
    [CheckPoint_Id] int  NOT NULL
);
GO

-- Creating table 'CheckPoints'
CREATE TABLE [dbo].[CheckPoints] (
    [Id] int IDENTITY(1,1) NOT NULL,
    [Title] nvarchar(max)  NOT NULL,
    [Longtitude] nvarchar(max)  NOT NULL,
    [Latitude] nvarchar(max)  NOT NULL,
    [Description] nvarchar(max)  NOT NULL
);
GO

-- Creating table 'Courses'
CREATE TABLE [dbo].[Courses] (
    [Id] int IDENTITY(1,1) NOT NULL,
    [Title] nvarchar(max)  NOT NULL,
    [Description] nvarchar(max)  NOT NULL,
    [Duration] time  NOT NULL
);
GO

-- Creating table 'CoursePoints'
CREATE TABLE [dbo].[CoursePoints] (
    [CheckPointRId] int  NOT NULL,
    [CourseRId] int  NOT NULL
);
GO

-- --------------------------------------------------
-- Creating all PRIMARY KEY constraints
-- --------------------------------------------------

-- Creating primary key on [Id] in table 'Submits'
ALTER TABLE [dbo].[Submits]
ADD CONSTRAINT [PK_Submits]
    PRIMARY KEY CLUSTERED ([Id] ASC);
GO

-- Creating primary key on [Id] in table 'Comments'
ALTER TABLE [dbo].[Comments]
ADD CONSTRAINT [PK_Comments]
    PRIMARY KEY CLUSTERED ([Id] ASC);
GO

-- Creating primary key on [Id] in table 'Clients'
ALTER TABLE [dbo].[Clients]
ADD CONSTRAINT [PK_Clients]
    PRIMARY KEY CLUSTERED ([Id] ASC);
GO

-- Creating primary key on [Id] in table 'Users'
ALTER TABLE [dbo].[Users]
ADD CONSTRAINT [PK_Users]
    PRIMARY KEY CLUSTERED ([Id] ASC);
GO

-- Creating primary key on [Id] in table 'Ships'
ALTER TABLE [dbo].[Ships]
ADD CONSTRAINT [PK_Ships]
    PRIMARY KEY CLUSTERED ([Id] ASC);
GO

-- Creating primary key on [Id] in table 'CostShips'
ALTER TABLE [dbo].[CostShips]
ADD CONSTRAINT [PK_CostShips]
    PRIMARY KEY CLUSTERED ([Id] ASC);
GO

-- Creating primary key on [Id] in table 'Cabins'
ALTER TABLE [dbo].[Cabins]
ADD CONSTRAINT [PK_Cabins]
    PRIMARY KEY CLUSTERED ([Id] ASC);
GO

-- Creating primary key on [Id] in table 'Excursions'
ALTER TABLE [dbo].[Excursions]
ADD CONSTRAINT [PK_Excursions]
    PRIMARY KEY CLUSTERED ([Id] ASC);
GO

-- Creating primary key on [Id] in table 'CheckPoints'
ALTER TABLE [dbo].[CheckPoints]
ADD CONSTRAINT [PK_CheckPoints]
    PRIMARY KEY CLUSTERED ([Id] ASC);
GO

-- Creating primary key on [Id] in table 'Courses'
ALTER TABLE [dbo].[Courses]
ADD CONSTRAINT [PK_Courses]
    PRIMARY KEY CLUSTERED ([Id] ASC);
GO

-- Creating primary key on [CheckPointRId], [CourseRId] in table 'CoursePoints'
ALTER TABLE [dbo].[CoursePoints]
ADD CONSTRAINT [PK_CoursePoints]
    PRIMARY KEY NONCLUSTERED ([CheckPointRId], [CourseRId] ASC);
GO

-- --------------------------------------------------
-- Creating all FOREIGN KEY constraints
-- --------------------------------------------------

-- Creating foreign key on [ShipId] in table 'Cabins'
ALTER TABLE [dbo].[Cabins]
ADD CONSTRAINT [FK_ShipCabin]
    FOREIGN KEY ([ShipId])
    REFERENCES [dbo].[Ships]
        ([Id])
    ON DELETE NO ACTION ON UPDATE NO ACTION;

-- Creating non-clustered index for FOREIGN KEY 'FK_ShipCabin'
CREATE INDEX [IX_FK_ShipCabin]
ON [dbo].[Cabins]
    ([ShipId]);
GO

-- Creating foreign key on [Cost_Id] in table 'Ships'
ALTER TABLE [dbo].[Ships]
ADD CONSTRAINT [FK_ShipCost]
    FOREIGN KEY ([Cost_Id])
    REFERENCES [dbo].[CostShips]
        ([Id])
    ON DELETE NO ACTION ON UPDATE NO ACTION;

-- Creating non-clustered index for FOREIGN KEY 'FK_ShipCost'
CREATE INDEX [IX_FK_ShipCost]
ON [dbo].[Ships]
    ([Cost_Id]);
GO

-- Creating foreign key on [CheckPointRId] in table 'CoursePoints'
ALTER TABLE [dbo].[CoursePoints]
ADD CONSTRAINT [FK_CheckPointRCoursePointR]
    FOREIGN KEY ([CheckPointRId])
    REFERENCES [dbo].[CheckPoints]
        ([Id])
    ON DELETE NO ACTION ON UPDATE NO ACTION;
GO

-- Creating foreign key on [CourseRId] in table 'CoursePoints'
ALTER TABLE [dbo].[CoursePoints]
ADD CONSTRAINT [FK_CourseRCoursePointR]
    FOREIGN KEY ([CourseRId])
    REFERENCES [dbo].[Courses]
        ([Id])
    ON DELETE NO ACTION ON UPDATE NO ACTION;

-- Creating non-clustered index for FOREIGN KEY 'FK_CourseRCoursePointR'
CREATE INDEX [IX_FK_CourseRCoursePointR]
ON [dbo].[CoursePoints]
    ([CourseRId]);
GO

-- Creating foreign key on [CheckPoint_Id] in table 'Excursions'
ALTER TABLE [dbo].[Excursions]
ADD CONSTRAINT [FK_ExcursionRCheckPointR]
    FOREIGN KEY ([CheckPoint_Id])
    REFERENCES [dbo].[CheckPoints]
        ([Id])
    ON DELETE NO ACTION ON UPDATE NO ACTION;

-- Creating non-clustered index for FOREIGN KEY 'FK_ExcursionRCheckPointR'
CREATE INDEX [IX_FK_ExcursionRCheckPointR]
ON [dbo].[Excursions]
    ([CheckPoint_Id]);
GO

-- Creating foreign key on [Course_Id] in table 'Ships'
ALTER TABLE [dbo].[Ships]
ADD CONSTRAINT [FK_CourseRShipR]
    FOREIGN KEY ([Course_Id])
    REFERENCES [dbo].[Courses]
        ([Id])
    ON DELETE NO ACTION ON UPDATE NO ACTION;

-- Creating non-clustered index for FOREIGN KEY 'FK_CourseRShipR'
CREATE INDEX [IX_FK_CourseRShipR]
ON [dbo].[Ships]
    ([Course_Id]);
GO

-- --------------------------------------------------
-- Script has ended
-- --------------------------------------------------